﻿using System;
using System.Data;
using Code;
using System.Collections.Generic;

namespace E2_WEBSITE.Code.Admin
{
    public class AdsDataAccess
    {
        DataProvider provider = DataProvider.GlobalDataProvider;
        string sql = string.Empty;

        public DataTable GetAds()
        {
            try
            {
                sql = string.Format("SELECT a.[id] as ID" +
                                          ",a.[NgayBatDau] as NgayBatDau" +
                                          ",a.[NgayKetThuc] as NgayKetThuc" +
                                          ",a.[ViTri] as ViTri" +
                                          ",a.[Gia] as Gia" +
                                          ",u.[Ten] as KhachHang " +
                                      "FROM [COMPUTERSHOPDB].[dbo].[QUANG_CAO] a, [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] u " +
                                      "WHERE a.[idKhachHang] = u.[id]");
                DataTable data = provider.ExecuteQueryDataTable(sql);
                return data;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public List<AdsInfo> GetListAds(DateTime dtNgayBatDau, DateTime dtNgayKetThuc)
        {
            try
            {       
                string sNgayBatDau = dtNgayBatDau.ToString("MM/dd/yyyy");
                string sNgayKetThuc = dtNgayKetThuc.ToString("MM/dd/yyyy");

                sql = string.Format("SELECT a.[id] as ID" +
                                          ",a.[NgayBatDau] as NgayBatDau" +
                                          ",a.[NgayKetThuc] as NgayKetThuc" +
                                          ",a.[ViTri] as ViTri" +
                                          ",a.[Gia] as Gia" +
                                          ",a.[HinhAnh] as HinhAnh " +
                                          ",a.[DuongDan] as DuongDan " +                                          
                                      "FROM QUANG_CAO a " +
                                      "WHERE a.[NgayBatDau] <= '{0}' AND a.[NgayKetThuc] >= '{1}' ORDER BY a.[ViTri]", sNgayBatDau, sNgayKetThuc);
                
                DataTable data = provider.ExecuteQueryDataTable(sql);
                if (data != null)
                {
                    List<AdsInfo> lst = new List<AdsInfo>();
                    AdsInfo ad;
                    foreach (DataRow row in data.Rows)
                    {
                        DateTime ngayBatDau = DateTime.Parse(row["NgayBatDau"].ToString());
                        DateTime ngayKetThuc = DateTime.Parse(row["NgayKetThuc"].ToString());
                        int pos = int.Parse(row["ViTri"].ToString());
                        int cost = int.Parse(row["Gia"].ToString());
                        string img = row["HinhAnh"].ToString();
                        string url = row["DuongDan"].ToString();

                        ad = new AdsInfo(ngayBatDau, ngayKetThuc, pos, cost, img, url);
                        lst.Add(ad);
                    }

                    return lst;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {                
                throw ex;
            }            
        }

        public AdsInfo GetAdsById(int aid)
        {
            try
            {
                AdsInfo ads = null;
                sql = string.Format("SELECT a.[id] as ID" +
                                          ",a.[NgayBatDau] as NgayBatDau" +
                                          ",a.[NgayKetThuc] as NgayKetThuc" +
                                          ",a.[ViTri] as ViTri" +
                                          ",a.[Gia] as Gia" +
                                          ",a.[idKhachHang] as idKhachHang" +
                                          ",u.[Ten] as KhachHang" +
                                          ",a.[HinhAnh] as HinhAnh" + 
                                          ",a.[DuongDan] as DuongDan " +
                                      "FROM [COMPUTERSHOPDB].[dbo].[QUANG_CAO] a, [COMPUTERSHOPDB].[dbo].[NGUOI_DUNG] u " +
                                      "WHERE a.[idKhachHang] = u.[id] AND a.[id] = {0}", aid);

                DataTable data = provider.ExecuteQueryDataTable(sql);
                if (data.Rows.Count > 0)
                {
                    ads = AdsInfo.Load(data);
                }
                return ads;
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public bool InsertAds(int aid, int UserId, AdsInfo ads)
        {
            try
            {
                sql = string.Format("INSERT INTO [COMPUTERSHOPDB].[dbo].[QUANG_CAO] " +
                                           "([id]" +
                                           ",[NgayBatDau]" +
                                           ",[NgayKetThuc]" +
                                           ",[ViTri]" +
                                           ",[Gia]" +
                                           ",[idKhachHang]" +
                                           ",[HinhAnh]" + 
                                           ",[DuongDan]) " +
                                     "VALUES " +
                                           "({0}" +
                                           ",'{1}'" +
                                           ",'{2}'" +
                                           ",{3}" +
                                           ",{4}" +
                                           ",{5}" +
                                           ",'{6}'" +
                                           ",'{7}')",
                                           aid,
                                           ads.StartTime.ToString(Utilities.DateTimeFormatString),
                                           ads.EndTime.ToString(Utilities.DateTimeFormatString),
                                           ads.Position,
                                           ads.Price,
                                           UserId,
                                           ads.Image,
                                           ads.Navigate);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public int GetAdsFreeId()
        {
            try
            {
                int aid = 0;
                sql = string.Format("SELECT MAX([id]) as ID " +
                                        "FROM [COMPUTERSHOPDB].[dbo].[QUANG_CAO]");
                DataTable table = provider.ExecuteQueryDataTable(sql);
                if (table.Rows.Count > 0 && !table.Rows[0]["ID"].ToString().Equals(string.Empty))
                {
                    aid = int.Parse(table.Rows[0]["ID"].ToString());
                }
                return (aid + 1);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public bool UpdateAds(int aid, int UserId, AdsInfo ads)
        {
            try
            {
                sql = string.Format("UPDATE [COMPUTERSHOPDB].[dbo].[QUANG_CAO] " +
                                       "SET [NgayBatDau] = '{1}'" +
                                          ",[NgayKetThuc] = '{2}'" +
                                          ",[ViTri] = {3}" +
                                          ",[Gia] = {4}" +
                                          ",[idKhachHang] = {5}" +
                                          ",[HinhAnh] = '{6}'" +
                                          ",[DuongDan] = '{7}' " +
                                     "WHERE [id] = {0}",
                                     aid,
                                     ads.StartTime.ToString(Utilities.DateTimeFormatString),
                                     ads.EndTime.ToString(Utilities.DateTimeFormatString),
                                     ads.Position,
                                     ads.Price,
                                     UserId,
                                     ads.Image,
                                     ads.Navigate);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public bool DeleteAds(int aid)
        {
            try
            {
                sql = string.Format("DELETE FROM [COMPUTERSHOPDB].[dbo].[QUANG_CAO] " +
                                        "WHERE [id] = {0}", aid);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                
                throw;
            }
        }
    }
}